<?php
/**
 * フリーワード検索用SQL作成クラス<br />
 *
 * @package apamanshop
 * @author SS鈴木
 * @copyright
 */
class Rstobi_Model_FreewordSearch extends Rstobi_Model_Base
{
    /**
     * フリーワード検索用From句取得
     *
	 * @access	public
	 * @param	array
	 * @param	array
	 * @param	boolean
	 * @return  string
     */
	public function getFromForFreewordsearch($params ,&$db_params ,$recommendFlg = false)
	{
		/** 物件検索でも使用出来るように作成しているが、 **/
		/** パフォーマンスの観点から現在はレコメンド以外では使用してない **/
		/** ⇒レコメンド用を下記に新規作成したため、現在はどこからも使用されていないが念のため残しておく。 **/
		
		$whereParam = array();
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);

		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();

		// フリーワード検索用From句SQL作成
		$strFrom = $this->generatFerrFromSql($whereParam, $recommendFlg);
		
		// フリーワード検索用Where句SQL作成
		$strFrom .= $this->generatFerrWhereSql();
		
		return $strFrom;
	}
	
    /**
     * フリーワード検索用From句取得
     *
	 * @access	public
	 * @param	array
	 * @param	array
	 * @param	boolean
	 * @return  string
     */
	public function getFromForFreewordsearch_s($params ,&$db_params ,$recommendFlg = false)
	{
		/** 物件検索でも使用出来るように作成しているが、 **/
		/** パフォーマンスの観点から現在はレコメンド以外では使用してない **/
		/** 完全にFrom句のみ取得するように修正版 **/
		
		$whereParam = array();
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);

		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();

		// フリーワード検索用From句SQL作成
		$strFrom = $this->generatFerrFromSql($whereParam, $recommendFlg);
		
		return $strFrom;
	}
	
	/**
	 * 有効な全文検索用テーブル取得処理
	 *
	 * @access	public
	 * @return  string
	 */
	public function getFreeWordTargetTbl()
	{
		$db_params = array();
		// 有効な全文検索用テーブル名取得SQL
		$sql = $this->generateSearchTblSql();

		$data = array();
		$data = $this->dbGetData($sql, $db_params);

		return $data['VAL1'];
	}

	/**
	 * 検索ワードログ格納処理
	 *
	 * @access	public
	 * @param	array
	 * @param	object
	 * @return  string
	 *
	 */
	public function insertFreeWordLog($params,$db)
	{
		$db_params = array();

		// 検索ワードログ格納SQL取得
		$sql = $this->generateInsertSql($params);

		$data = array();
		$data = $this->dbExecQuery($sql, $db_params,$db);

		return;
	}

	/**
	 * フリーワード文字列加工処理
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function freeWordWhere($freeWord)
	{
		$freewordFix = $freeWord;

		// 記号をエスケープ
		$freewordFix = str_replace("-","\\-",$freewordFix);
		$freewordFix = str_replace("－","\\-",$freewordFix);
		$freewordFix = str_replace("　"," ",$freewordFix);
//#332 add start
		$freewordFix = trim($freewordFix);
//#332 add end

		// 区切り文字を  , に統一
		$freewordFix = preg_replace('( {1,})',"kugiri",$freewordFix);

		// 表記揺れ変換、類義語変換処理
		$freewordFix = $this->makeSameMeanWordToOrgWord($freewordFix);

		// 区切り文字を  AND に置換し、OracleText予約後を全てエスケープすべく、ワードの両側を｛｝でくくる
		if (strpos($freewordFix,"kugiri") !== false) {
			$freewordFix = "{".preg_replace("(kugiri)","} AND {",$freewordFix,true)."}";
		}
		
		return $freewordFix;
	}

	/**
	 * 同義語変換処理
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function makeSameMeanWordToOrgWord($strword)
	{
		$strwk = "";
		$db_params = array();

		$aryWord = explode("kugiri",$strword);

		foreach ($aryWord as $word){

			// 同義語変換データ取得SQL
			$sql = $this->generateOrgWordSql($word);

			$data = array();
			$data = $this->dbGetData($sql, $db_params);

			// データが設定されていた場合
			if ( !empty($data) ){
				$strwk = $strwk."({".$word."} OR {".$data["ORG_WORD"]."})kugiri";
				$str_CngWordBfrAft = $str_CngWordBfrAft."「".$word."」と同じ意味を持つ「".$data["ORG_WORD"]."」についても検索しました。<br>";
			}
			else{
				$strwk = $strwk.$word."kugiri";
			}
		}

		//最後尾のカンマ除去
		$strwk = preg_replace("(kugiri$)","",$strwk);

		return $strwk;
	}
	
	/**
	 * 同義語変換データ取得SQL
	 *
	 * @access	public
	 * @param   string
	 * @return  string
	 */
	public function generateOrgWordSql($strWord)
	{
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        ORG_WORD";
		$sql .= "    FROM";
		$sql .= "        M87SAME_MEAN_WORD";
		$sql .= "    WHERE";
		$sql .= "        SAME_MEAN_WORD = '".$strWord."'";
		$sql .= "        AND PROC_FLG = 1";

		return $sql;

	}

	/**
	 * 有効な全文検索用テーブル取得
	 *
	 * @access	public
	 * @return  string
	 */
	public function generateSearchTblSql()
	{
		$sql = "";
		$sql .="SELECT";
		$sql .="        VAL1";
		$sql .="    FROM";
		$sql .="        T97PROC_CONTROL";
		$sql .="    WHERE";
		$sql .="        CTR_CD = 'AVAIL_BUKKEN_FULLTEXT_TBL'";

		return $sql;
	}

	/**
	 * 検索ワードログ格納SQL
	 *
	 * @access	public
	 * @return  string
	 */
	public function generateInsertSql($params)
	{
		$sql = "";
		$sql .="INSERT";
		$sql .="    INTO";
		$sql .="        W76FREEWORD_LOG(";
		$sql .="            LOG_ID";
		$sql .="            ,USER_IP";
		$sql .="            ,WORD_STR";
		$sql .="            ,HIT_COUNT";
		$sql .="            ,REG_DATE";
		$sql .="        )";
		$sql .="    VALUES";
		$sql .="        (";
		$sql .="            SEQ_FREEWORD_LOG.NEXTVAL";
		$sql .="            ,'".$_SERVER['REMOTE_ADDR']."'";
		$sql .="            ,'".$params["freeword"]."'";
		$sql .="            ,5188";
		$sql .="            ,SYSDATE";
		$sql .="        )";
		
		return $sql;
	}
	
	/**
	 * フリーワード検索用from句SQL取得
	 *
	 * @access	public
	 * @param   array
	 * @param   boolean
	 * @return  string
	 */
	public function generatFerrFromSql($whereParam ,$recommendFlg)
	{
		/** 物件検索でも使用出来るように作成しているが、 **/
		/** パフォーマンスの観点から現在はレコメンド以外では使用してない **/
		
		$rows = 10000;
		if($recommendFlg){ $rows = 100; }
		
		$strFrom  = " FROM";
		$strFrom .= " (SELECT";
		$strFrom .= "         TENPO_CD";
		$strFrom .= "        ,ORIGINAL_HEYA_CD01";
		$strFrom .= "        ,KASHIDASHI_KUBUN";
		$strFrom .= "     FROM";
		$strFrom .= "         (SELECT";
		$strFrom .= "             /*+ FIRST_ROWS(".$rows.") PARALLEL (T, 3) */";
		$strFrom .= "             TENPO_CD";
		$strFrom .= "            ,ORIGINAL_HEYA_CD01";
		$strFrom .= "            ,KASHIDASHI_KUBUN";
		$strFrom .= "         FROM ";
		$strFrom .=               $whereParam['targetTbl'];
		$strFrom .= "         WHERE";
		$strFrom .= "             (CONTAINS( FULL_TEXT ,'".$whereParam['freeWord']."' ,1 ) > 0)";
		$strFrom .= "         )";
		$strFrom .= "     WHERE";
		$strFrom .= "         ROWNUM < ".($rows+1);
		$strFrom .= "     ) FERR";
		$strFrom .= " ,T03WEB_HEYA T03";
		
		return $strFrom;
	}
	
	/**
	 * フリーワード検索用where句SQL取得
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function generatFerrWhereSql()
	{
		/** 物件検索でも使用出来るように作成しているが、 **/
		/** パフォーマンスの観点から現在はレコメンド以外では使用してない **/
		
		$strWhere  = " WHERE";
		$strWhere .= "         FERR.TENPO_CD = T03.T03_TENPO_CD";
		$strWhere .= "     AND FERR.ORIGINAL_HEYA_CD01 = T03.T03_ORIGINAL_HEYA_CD01";
		$strWhere .= "     AND FERR.KASHIDASHI_KUBUN = T03.T03_KYAKUDUKE_KUBUN";
		
		return $strWhere;
	}
	
	/**
	 * 物件データ取得SQL
	 *
	 * @access	public
	 * @param   array
	 * @return  string
	 */
	public function getBukenData($params, $bukenCountFrom, $bukenCountTo)
	{
		$db_params = array();
		
		$this->_model_Sort = new Rstobi_Model_Sort();
		$defaultSort = $this->_model_Sort->getDefaultSort($params);
		
		$sql = $this->getSelect($params);
		
		if($this->chkParams($params) && $params['SORT_COLUMN'] == $defaultSort['SORT_COLUMN'] && $params['SORT_ORDER'] == $defaultSort['SORT_ORDER'])
		{
			// 基本条件なし、こだわり条件なし、デフォルトソートの場合、専用SQL
			$sql .= $this->getSqlFrastOnly($params, $bukenCountFrom, $bukenCountTo);
		}
		else
		{
			$sql .= $this->getSql($params, $bukenCountFrom, $bukenCountTo, &$db_params);
		}
		$sql .= "     ,T03WEB_HEYA T03";
		//$sql .= $this->_model_rstobi_common->getCampFrom($params);		//家電部屋キャンペーン
		$sql .= " WHERE";
		$sql .= "         FREE.TENPO_CD = T03.T03_TENPO_CD";
		$sql .= "     AND FREE.ORIGINAL_HEYA_CD01 = T03.T03_ORIGINAL_HEYA_CD01";
		$sql .= "     AND FREE.KASHIDASHI_KUBUN = T03.T03_KASHIDASHI_KUBUN";
		//$sql .= "     AND T03.T03_HEYA_CD = ICON.HEYA_CD(+)";			//家電部屋キャンペーン
		$sql .= " ORDER BY";
		$sql .= "     SORT_COL";
		$sql .= "     ".$params['SORT_ORDER'];
		
		//$sql .= "     ,T03_ORIGINAL_HEYA_CD01 ,RANDOM";		// ランダム表示対応
		
		// 2010/12/24 ランダム表示対応
		//$sql .= "     , SUBSTR(TO_CHAR(T03_KOUSHINBI, 'yyyymmddhh24mi'),0,11) ,RANDOM";  // ランダム表示対応
		$sql .= "     , TO_CHAR(T03_TOUROKUBI, 'yyyymmdd') DESC ,RANDOM";  // ランダム表示対応
		
		return $this->dbGetList($sql, $db_params);
	}
	
	
	/**
	 * 店舗データ件数取得SQL
	 *
	 * @access	public
	 * @param   array
	 * @return  string
	 */
	public function getSelectCountFreeForShop($params, &$db_params)
	{
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);
		
		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();
		
		$sql  = " SELECT";
		$sql .= "     count(*) AS SHOP_COUNT";
		$sql .= " FROM";
		$sql .= "     (SELECT /*+ PARALLEL (FULTXT, 3) */";
		$sql .= "         T03.T03_TENPO_CD";
		$sql .= "     FROM";
		$sql .= "        (SELECT /*+ FIRST_ROWS(500) */";
		$sql .= "             FULTXT.ORIGINAL_HEYA_CD01";
		$sql .= "         FROM ";
		$sql .=               $whereParam["targetTbl"]." FULTXT";
		$sql .= "            ,RENKEI_TENPO_WORK RENKEI";
		$sql .= "         WHERE";
		$sql .= "                 (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0)";
		$sql .= "              AND FULTXT.TENPO_CD = RENKEI.TENPO_CD";
		$sql .= "              AND RENKEI.TENPO_CD <> '00000002'";
		$sql .= "              AND RENKEI.IKOU_FLAG = 1";
		$sql .= "              AND TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD') ";
		$sql .=                $this->getFreeKihonWhere($params ,&$db_params);
							   if($params['PrefCd'][0] <> '')
							   {
								   $db_params['strPrefCd'] = abs($params['PrefCd'][0]);
								   $sql .= " AND FULTXT.TODOUFUKEN = :strPrefCd";
							   }
		$sql .= "              AND ROWNUM < 501";
		$sql .= "        ) FULTXT";
		$sql .= "        ,T03WEB_HEYA T03";
		$sql .= "     WHERE";
		$sql .= "         FULTXT.ORIGINAL_HEYA_CD01 = T03.T03_ORIGINAL_HEYA_CD01";
		$sql .= "     GROUP BY";
		$sql .= "         T03_TENPO_CD)";
		
		return $sql;
	}
	
	/**
	 * 店舗データ取得SQL
	 *
	 * @access	public
	 * @param   array
	 * @return  string
	 */
	public function getSelectFreeForShop($params, &$db_params, $shopCountFrom, $shopCountTo)
	{
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);
		
		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();
		
		$db_params['bukenFrom'] = $shopCountFrom;
		$db_params['bukenTo']   = $shopCountTo;
		
		$sql  = " SELECT";
		$sql .= "     *";
		$sql .= " FROM";
		$sql .= "     (SELECT";
		$sql .= "          ROWNUM AS T03_DISP_ROW";
		$sql .= "         ,T03_HEYA.*";
		
		//2010/12/24 ランダム表示対応
		$sql .= "         ,CEIL(DBMS_RANDOM.VALUE*1000000) RANDOM";
		
		
		$sql .= "      FROM";
		$sql .= "         ( SELECT";
		$sql .= "               M05_SHOP.M05_TENPO_CD";
		$sql .= "              ,M05_SHOP.M05_TENPO_NAME";
		$sql .= "              ,M05_SHOP.M05_HOUZIN_CD";
		$sql .= "              ,M05_SHOP.M05_OEM";
		$sql .= "              ,M05_SHOP.M05_TENPO_TEL01";
		$sql .= "              ,M05_SHOP.M05_TENPO_YUUBIN";
		$sql .= "              ,M05_SHOP.M05_JYUUSHO_TEXT";
		$sql .= "              ,M05_SHOP.M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "              ,M03_SHOP.M03_ENSEN_NAME";
		$sql .= "              ,M03_SHOP.M03_EKI_NAME";
		$sql .= "              ,M05_SHOP.M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "              ,M05_SHOP.M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "              ,M05_SHOP.M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "              ,M05_SHOP.M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "              ,M05_SHOP.M05_EIGYOU_ZIKAN_BIKOU";
		$sql .= "              ,M05_SHOP.M05_HOUZINMEI";
		$sql .= "              ,M05_SHOP.M05_TEIKYUUBI";
		$sql .= "              ,M05_SHOP.M05_TEIKYUUBI_BIKOU";
		$sql .= "              ,A06.A06_BUKKEN_CNT";
		for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
			$sql .= "          ,M93.M93_CONNECTION_NO".$call_no;
			$sql .= "          ,M93.M93_CONTROL_FLAG".$call_no;
		}
		$sql .= '              ,M93.M93_USE_KBN';
		$sql .= '              ,M93.M93_START_DATE';
		$sql .= '              ,M93.M93_DEL_DATE';
		$sql .= "              ,SUBSTR(M05S.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG";
		$sql .= "           FROM";
		$sql .= "              (SELECT /*+ PARALLEL (FULTXT, 3) */";
		$sql .= "                  T03.T03_TENPO_CD";
		$sql .= "               FROM";
		$sql .= "                 (SELECT ";
		$sql .= "                      FULTXT.ORIGINAL_HEYA_CD01";
		$sql .= "                  FROM ";
		$sql .=                        $whereParam["targetTbl"]." FULTXT";
		$sql .= "                     ,RENKEI_TENPO_WORK RENKEI";
		$sql .= "                  WHERE";
		$sql .= "                         (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0)";
										if($params['PrefCd'][0] <> '')
										{
											$db_params['strPrefCd'] = abs($params['PrefCd'][0]);
											$sql .= " AND FULTXT.TODOUFUKEN = :strPrefCd";
										}
		$sql .= "                      AND FULTXT.TENPO_CD = RENKEI.TENPO_CD";
		$sql .= "                      AND FULTXT.TENPO_CD = RENKEI.TENPO_CD";
		$sql .= "                      AND RENKEI.TENPO_CD <> '00000002'";
		$sql .= "                      AND RENKEI.IKOU_FLAG = 1";
		$sql .= "                      AND TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD') ";
		$sql .=                        $this->getFreeKihonWhere($params ,&$db_params);
		$sql .= "                      AND ROWNUM < 10001 ) FULTXT";
		$sql .= "                  ,T03WEB_HEYA T03";
		$sql .= "               WHERE";
		$sql .= "                   FULTXT.ORIGINAL_HEYA_CD01 = T03.T03_ORIGINAL_HEYA_CD01";
									if($params['PrefCd'][0] <> '')
									{
										$db_params['strPrefCd_t03'] = $params['PrefCd'][0];
										$sql .= " AND T03.T03_TODOUFUKEN_CD = :strPrefCd_t03";
									}
		$sql .= "               GROUP BY";
		$sql .= "                   T03_TENPO_CD) T";
		$sql .= "             ,M05TENPO_INFO M05_SHOP";
		$sql .= "             ,M03EKI M03_SHOP";
		$sql .= "             ,A06TENPO_BUKKEN_COUNT A06";
		$sql .= "             ,M93CALLNOTE M93";		// 2013/08 WEBCALLING→CALLNOTE 切替
		$sql .= "             ,M05TENPO_SERVICE M05S";
		$sql .= "           WHERE";
		$sql .= "                 M05_SHOP.M05_TENPO_CD   = T.T03_TENPO_CD";
		$sql .= "             AND M05_SHOP.M05_EKI_CD01   = M03_SHOP.M03_EKI_CD(+)";
		$sql .= "             AND M05_SHOP.M05_ENSEN_CD01 = M03_SHOP.M03_ENSEN_CD(+)";
		$sql .= "             AND M05_SHOP.M05_TENPO_CD   = A06.A06_TENPO_CD(+)";
		$sql .= "             AND M05_SHOP.M05_TENPO_CD   = M93.M93_TENPO_CD(+)";
		$sql .= "             AND M05_SHOP.M05_TENPO_CD   = M05S.M05_TENPO_CD) T03_HEYA";
		$sql .= "     )";
		$sql .= " WHERE";
		$sql .= "    T03_DISP_ROW between :bukenFrom and :bukenTo";
		
		return $sql;
	}
	
	/**
	 * 物件データ取得SQL
	 *
	 * @access	private
	 * @param   array
	 * @return  string
	 */
	private function getSql($params, $bukenCountFrom, $bukenCountTo, &$db_params)
	{
		
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);
		
		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();
		
		$sql  =" FROM";
		$sql .="    (SELECT";
		$sql .="        *";
		$sql .="     FROM";
		$sql .="     	(SELECT";
		$sql .="         	TENPO_CD ,";
		$sql .="         	ORIGINAL_HEYA_CD01 ,";
		$sql .="         	KASHIDASHI_KUBUN ,";
		$sql .="         	SORT_COL";
		$sql .="     	FROM";
		$sql .="         	(SELECT";
		$sql .="             	TENPO_CD ,";
		$sql .="             	ORIGINAL_HEYA_CD01 ,";
		$sql .="             	KASHIDASHI_KUBUN ,";
		$sql .="             	SORT_COL";
		$sql .="         	FROM";
		$sql .="             	(SELECT";
		$sql .="                 	/*+ FIRST_ROWS(500) PARALLEL (T, 3) */ T.TENPO_CD ,";
		$sql .="                 	ORIGINAL_HEYA_CD01 ,";
		$sql .="                 	KASHIDASHI_KUBUN , ";
		$sql .=                     $params['SORT_COLUMN_FOR_FREE'] ." AS SORT_COL";
		$sql .="             	FROM ";
		$sql .=                  	$whereParam["targetTbl"] ." T,";
		$sql .="                 	RENKEI_TENPO_WORK RENKEI";
		$sql .="             	WHERE";
		$sql .="     	            (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0) AND";
		$sql .="                 	RENKEI.TENPO_CD = T.TENPO_CD AND";
		$sql .="                 	RENKEI.TENPO_CD <> '00000002' AND";
		$sql .="                 	RENKEI.IKOU_FLAG = 1 AND";
		$sql .="                 	TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD')";
									if($params['PrefCd'][0] <> '')
									{
										$db_params['strPrefCd'] = abs($params['PrefCd'][0]);
										$sql .= " AND T.TODOUFUKEN = :strPrefCd";
									}
		$sql .= 					$this->getFreeKihonWhere($params ,&$db_params);
		$sql .="             	)";
		$sql .="         	WHERE";
		$sql .="             	ROWNUM < 1000 + 1";
		$sql .="         	ORDER BY";
		$sql .="             	SORT_COL ".$params['SORT_ORDER']." )";
		$sql .="     	WHERE";
		$sql .="         	ROWNUM < ". ($bukenCountTo + 1);
		$sql .="     	MINUS";
		$sql .="     	SELECT";
		$sql .="     	    TENPO_CD ,";
		$sql .="     	    ORIGINAL_HEYA_CD01 ,";
		$sql .="     	    KASHIDASHI_KUBUN ,";
		$sql .="     	    SORT_COL";
		$sql .="     	FROM";
		$sql .="     	    (SELECT";
		$sql .="     	        TENPO_CD ,";
		$sql .="     	        ORIGINAL_HEYA_CD01 ,";
		$sql .="     	        KASHIDASHI_KUBUN ,";
		$sql .="     	        SORT_COL";
		$sql .="     	    FROM";
		$sql .="     	        (SELECT";
		$sql .="     	            /*+ FIRST_ROWS(500) PARALLEL (T, 3) */ T.TENPO_CD ,";
		$sql .="     	            ORIGINAL_HEYA_CD01 ,";
		$sql .="     	            KASHIDASHI_KUBUN ,";
		$sql .=                     $params['SORT_COLUMN_FOR_FREE'] ." AS SORT_COL";
		$sql .="     	        FROM ";
		$sql .=                  	$whereParam["targetTbl"] ." T,";
		$sql .="     	            RENKEI_TENPO_WORK RENKEI";
		$sql .="     	        WHERE";
		$sql .="     	            (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0) AND";
		$sql .="     	            RENKEI.TENPO_CD = T.TENPO_CD AND";
		$sql .="     	            RENKEI.TENPO_CD <> '00000002' AND";
		$sql .="     	            RENKEI.IKOU_FLAG = 1 AND";
		$sql .="     	            TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD')";
									if($params['PrefCd'][0] <> '')
									{
										$sql .= " AND T.TODOUFUKEN = :strPrefCd";
									}
		$sql .= 					$this->getFreeKihonWhere($params ,&$db_params);
		$sql .="     	        )";
		$sql .="     	    WHERE";
		$sql .="     	        ROWNUM < 1000 + 1";
		$sql .="     	    ORDER BY";
		$sql .="             	SORT_COL ".$params['SORT_ORDER']." )";
		$sql .="     	WHERE";
		$sql .="     		ROWNUM < ". $bukenCountFrom . ")";
		$sql .="    ) FREE";
		
		return $sql;
	}
	
	/**
	 * SELECT句取得
	 *
	 * @access	private
	 * @param   array
	 * @return  string
	 */
	private function getSelect($params)
	{
		$sql  = " SELECT";
		$sql .= "      T03.T03_HEYA_CD";
		$sql .= "     ,T03.T03_TATEMONO_CD";
		$sql .= "     ,T03_TENPO_CD";
		$sql .= "     ,T03.T03_ORIGINAL_TATEMONO_CD01";
		$sql .= "     ,T03.T03_ORIGINAL_TATEMONO_CD02";
		$sql .= "     ,T03.T03_ORIGINAL_HEYA_CD01";
		$sql .= "     ,T03.T03_ORIGINAL_HEYA_CD02";
		$sql .= "     ,T03.T03_TODOUFUKEN_CD";
		$sql .= "     ,T03.T03_SHIKUCHOUSON_CD";
		$sql .= "     ,T03.T03_CHOUMEI_CD";
		$sql .= "     ,T03.T03_CHOUMOKU_CD";
		$sql .= "     ,T03.T03_JYUUSHO_TEXT";
		$sql .= "     ,T03.T03_TATEMONO_TYPE_CD";
		$sql .= "     ,T03.T03_TATEMONO_TYPE";
		$sql .= "     ,T03.T03_TATEMONO_KOUZOU_CD";
		$sql .= "     ,T03.T03_TATEMONO_KOUZOU";
		$sql .= "     ,T03.T03_KAIDATE";
		$sql .= "     ,T03.T03_KOSUU";
		$sql .= "     ,T03.T03_KANSEINEN";
		$sql .= "     ,T03.T03_KANSEITSUKI";
		$sql .= "     ,T03.T03_KANSEIBI";
		$sql .= "     ,T03.T03_SHINCHIKU_FLAG";
		$sql .= "     ,T03.T03_TATEMONO_MENSEKI";
		$sql .= "     ,T03.T03_SHIKICHI_MENSEKI";
		$sql .= "     ,T03.T03_KAI";
		$sql .= "     ,T03.T03_HOUGAKU";
		$sql .= "     ,T03.T03_MADORI_CD";
		$sql .= "     ,T03.T03_MADORI";
		$sql .= "     ,T03.T03_SENYUU_MENSEKI";
		$sql .= "     ,T03.T03_YACHIN";
		$sql .= "     ,T03.T03_YACHIN_TAX";
		$sql .= "     ,T03.T03_KANRIHI";
		$sql .= "     ,T03.T03_KANRIHI_TAX";
		$sql .= "     ,T03.T03_KYOUEKIHI";
		$sql .= "     ,T03.T03_KYOUEKIHI_TAX";
		$sql .= "     ,T03.T03_SHIKIKIN";
		$sql .= "     ,T03.T03_REIKIN";
		$sql .= "     ,T03.T03_HOSHOUKIN";
		$sql .= "     ,T03.T03_SHOUKYAKU_KUBUN";
		$sql .= "     ,T03.T03_SHOUKYAKUKIN";
		$sql .= "     ,T03.T03_KOUSHINRYOU";
		$sql .= "     ,T03.T03_SHIKIKIN_TSUKISUU";
		$sql .= "     ,T03.T03_REIKIN_TSUKISUU";
		$sql .= "     ,T03.T03_HOSHOUKIN_TSUKISUU";
		$sql .= "     ,T03.T03_SHOUKYAKUKIN_TSUKISUU";
		$sql .= "     ,T03.T03_SHOUKYAKUKINRITSU";
		$sql .= "     ,T03.T03_KOUSHINRYOUTSUKISUU";
		$sql .= "     ,T03.T03_TESUURYOU_FLAG";
		$sql .= "     ,T03.T03_KOUSHINRYOU_FLAG";
		$sql .= "     ,T03.T03_NYUUKYO_NEN";
		$sql .= "     ,T03.T03_NYUUKYO_TSUKI";
		$sql .= "     ,T03.T03_NYUUKYO_ZIKI";
		$sql .= "     ,T03.T03_KOUTSUU_CD01";
		$sql .= "     ,T03.T03_ENSEN_CD01";
		$sql .= "     ,T03.T03_ENSEN_NAME";
		$sql .= "     ,T03.T03_EKI_CD01";
		$sql .= "     ,T03.T03_EKI_NAME";
		$sql .= "     ,T03.T03_BUS_GAISHA";
		$sql .= "     ,T03.T03_BUS_JYOUSHA";
		$sql .= "     ,T03.T03_BUS_STOP";
		$sql .= "     ,T03.T03_TOHO01";
		$sql .= "     ,T03.T03_KOUTSUU_BIKOU01";
		$sql .= "     ,T03.T03_KOUTSUU_TEXT01";
		$sql .= "     ,T03.T03_RENRAKU_EKI_CD01";
		$sql .= "     ,T03.T03_KOUTSUU_CD02";
		$sql .= "     ,T03.T03_ENSEN_CD02";
		$sql .= "     ,T03.T03_ENSEN_NAME02";
		$sql .= "     ,T03.T03_EKI_CD02";
		$sql .= "     ,T03.T03_EKI_NAME02";
		$sql .= "     ,T03.T03_BUS_GAISHA02";
		$sql .= "     ,T03.T03_BUS_JYOUSHA02";
		$sql .= "     ,T03.T03_BUS_STOP02";
		$sql .= "     ,T03.T03_TOHO02";
		$sql .= "     ,T03.T03_KOUTSUU_BIKOU02";
		$sql .= "     ,T03.T03_KOUTSUU_TEXT02";
		$sql .= "     ,T03.T03_RENRAKU_EKI_CD02";
		$sql .= "     ,T03.T03_KOUTSUU_CD03";
		$sql .= "     ,T03.T03_ENSEN_CD03";
		$sql .= "     ,T03.T03_ENSEN_NAME03";
		$sql .= "     ,T03.T03_EKI_CD03";
		$sql .= "     ,T03.T03_EKI_NAME03";
		$sql .= "     ,T03.T03_BUS_GAISHA03";
		$sql .= "     ,T03.T03_BUS_JYOUSHA03";
		$sql .= "     ,T03.T03_BUS_STOP03";
		$sql .= "     ,T03.T03_TOHO03";
		$sql .= "     ,T03.T03_KOUTSUU_BIKOU03";
		$sql .= "     ,T03.T03_KOUTSUU_TEXT03";
		$sql .= "     ,T03.T03_RENRAKU_EKI_CD03";
		$sql .= "     ,T03.T03_GAIKAN_GAZOU01";
		$sql .= "     ,T03.T03_GAIKAN_GAZOU02";
		$sql .= "     ,T03.T03_GAIKAN_GAZOU03";
		$sql .= "     ,T03.T03_NAISOU_GAZOU01";
		$sql .= "     ,T03.T03_NAISOU_GAZOU02";
		$sql .= "     ,T03.T03_NAISOU_GAZOU03";
		$sql .= "     ,T03.T03_NAISOU_GAZOU04";
		$sql .= "     ,T03.T03_NAISOU_GAZOU05";
		$sql .= "     ,T03.T03_NAISOU_GAZOU06";
		$sql .= "     ,T03.T03_NAISOU_GAZOU07";
		$sql .= "     ,T03.T03_NAISOU_GAZOU08";
		$sql .= "     ,T03.T03_NAISOU_GAZOU09";
		$sql .= "     ,T03.T03_NAISOU_GAZOU10";
		// 2014/04/28 Add H.Imamura 内装画像種別追加 Start
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE01';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE02';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE03';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE04';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE05';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE06';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE07';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE08';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE09';
		$sql .= '     ,T03.T03_NAISOU_GAZOU_TYPE10';
		// 2014/04/28 Add H.Imamura 内装画像種別追加 End
		$sql .= "     ,T03.T03_MADORIZU";
		$sql .= "     ,T03.T03_TOUROKUBI";
		$sql .= "     ,T03.T03_TOUROKUBI_STR";
		$sql .= "     ,T03.T03_SAKUSEIBI";
		$sql .= "     ,T03.T03_KOUSHINBI";
		$sql .= "     ,T03_FREERENT_FLAG";
		$sql .= "     ,T03_HEYAWARI_FLAG";
		$sql .= "     ,T03_MEYASU_CHINRYO";
		$sql .= "     ,T03_TEN_MEYASU_CHINRYO";
		$sql .= "     ,T03_GAIKAN_GAZOU_COMMON01";
		$sql .= "     ,T03_GAIKAN_GAZOU_COMMON02";
		$sql .= "     ,T03_GAIKAN_GAZOU_COMMON03";
		$sql .= "     ,T03_GAIKAN_GAZOU_COMMON04";
		$sql .= "     ,T03_GAIKAN_GAZOU_COMMON05";
		$sql .= "     ,T03_HAICHIZU_COMMON";
		$sql .= "     ,T03_SHUHEN_GAZOU_COMMON01";
		$sql .= "     ,T03_SHUHEN_GAZOU_COMMON02";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON01";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON02";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON03";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON04";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON05";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON06";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON07";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON08";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON09";
		$sql .= "     ,T03_NAISOU_GAZOU_COMMON10";
		$sql .= "     ,T03_MADORIZU_COMMON";
		$sql .= "     ,T03_PANORAMA_GAZOU_COMMON01";
		$sql .= "     ,T03_PANORAMA_GAZOU_COMMON02";
		$sql .= "     ,T03_FREE_GAZOU01";
		$sql .= "     ,T03_FREE_GAZOU_TYPE01";
		$sql .= "     ,T03_FREE_GAZOU02";
		$sql .= "     ,T03_FREE_GAZOU_TYPE02";
		$sql .= "     ,T03_FREE_GAZOU03";
		$sql .= "     ,T03_FREE_GAZOU_TYPE03";
		$sql .= "     ,T03_FREE_GAZOU04";
		$sql .= "     ,T03_FREE_GAZOU_TYPE04";
		$sql .= "     ,T03_FREE_GAZOU05";
		$sql .= "     ,T03_FREE_GAZOU_TYPE05";
		$sql .= "     ,T03_FREE_GAZOU06";
		$sql .= "     ,T03_FREE_GAZOU_TYPE06";
		$sql .= "     ,T03_FREE_GAZOU07";
		$sql .= "     ,T03_FREE_GAZOU_TYPE07";
		$sql .= "     ,T03_FREE_GAZOU08";
		$sql .= "     ,T03_FREE_GAZOU_TYPE08";
		$sql .= "     ,T03_FREE_GAZOU09";
		$sql .= "     ,T03_FREE_GAZOU_TYPE09";
		$sql .= "     ,T03_FREE_GAZOU10";
		$sql .= "     ,T03_FREE_GAZOU_TYPE10";
		$sql .= "     ,T03_FREE_GAZOU11";
		$sql .= "     ,T03_FREE_GAZOU_TYPE11";
		$sql .= "     ,T03_FREE_GAZOU12";
		$sql .= "     ,T03_FREE_GAZOU_TYPE12";
		$sql .= "     ,T03_FREE_GAZOU_COMMON01";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON01";
		$sql .= "     ,T03_FREE_GAZOU_COMMON02";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON02";
		$sql .= "     ,T03_FREE_GAZOU_COMMON03";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON03";
		$sql .= "     ,T03_FREE_GAZOU_COMMON04";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON04";
		$sql .= "     ,T03_FREE_GAZOU_COMMON05";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON05";
		$sql .= "     ,T03_FREE_GAZOU_COMMON06";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON06";
		$sql .= "     ,T03_FREE_GAZOU_COMMON07";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON07";
		$sql .= "     ,T03_FREE_GAZOU_COMMON08";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON08";
		$sql .= "     ,T03_FREE_GAZOU_COMMON09";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON09";
		$sql .= "     ,T03_FREE_GAZOU_COMMON10";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON10";
		$sql .= "     ,T03_FREE_GAZOU_COMMON11";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON11";
		$sql .= "     ,T03_FREE_GAZOU_COMMON12";
		$sql .= "     ,T03_FREE_GAZOU_TYPE_COMMON12";
		$sql .= "     ,trunc(";
		$sql .= "            months_between(";
		$sql .= "                           sysdate,";
		$sql .= "                           to_date(lpad(TO_CHAR(NVL(T03_KANSEINEN,'00')),4,'0')||lpad(TO_CHAR(NVL(T03_KANSEITSUKI,'00')),2,'0')||lpad(TO_CHAR(NVL(T03_KANSEIBI,'01')),2,'0'), 'yyyy.mm.dd')";
		$sql .= "                          ) /12";
		$sql .= "           ) AS T03_TIKU_NENSU";
		$sql .= "     ,CASE WHEN (( (sysdate - 7) < T03.T03_KOUSHINBI ) AND ( T03.T03_KIGENGIRE_SHUUSEI_FLAG <> 1 )) THEN 0 ELSE 1 END AS SHINCHAKU_FLAG";
		$sql .= "     ,T03_SHUHEN_GAZOU01";
		$sql .= "     ,T03_SHUHEN_GAZOU02";
		$sql .= "     ,T03_SHATAKU_TEKIYOU_FLAG";
		$sql .= "     ,T03_KASHIDASHI_KUBUN";
		$sql .= "     ,T03_SHOKI_HIYO";
		$sql .= "     ,T03_TEN_SHOKI_HIYO";
		$sql .= "     ,T03_KOUKOKU_KAISHA_CD";
		$sql .= "     ,T03_KOUKOKU_TOIAWASE_NO";
		$sql .= "     ,T03_GAIKAN_GAZOU04";
		$sql .= "     ,T03_GAIKAN_GAZOU05";
		$sql .= "     ,T03_MOTODUKE_FLAG";
		$sql .= "     ,T03_TENPO_DISTANCE";
		$sql .= "     ,T03_TEN_HEYAWARI_FLAG";
		$sql .= '     ,CEIL(DBMS_RANDOM.VALUE*1000000) RANDOM';		// ランダム表示対応
		$sql .= "     ,T03.T03_PR01";
		$sql .= "     ,T03.T03_PR02";
		$sql .= "     ,T03.T03_TEN_FREERENT_FLAG";
		$sql .= "     ,T03.T03_YACHIN_KANRI_KYOEKI_SUM";
		//$sql .= '     ,ICON.CAMPAIGN_ICON_FLAG';					// 家電部屋キャンペーン
		$sql .= Apm_Bukkenitems::getSelectPhraseForDisplayIconCampaign($params);
		
		return $sql;
	}
	
	/**
	 * 物件データ取得SQL（初回のみ）
	 *
	 * @access	private
	 * @param   array
	 * @return  string
	 */
	private function getSqlFrastOnly($params, $bukenCountFrom, $bukenCountTo)
	{
		// Where句内のフリーワード整形
		$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);
		
		// フリーワード検索対処テーブル取得
		$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();
		
		$sql  =" FROM";
		$sql .="    (SELECT";
		$sql .="        *";
		$sql .="     FROM";
		$sql .="     	(SELECT";
		$sql .="         	TENPO_CD ,";
		$sql .="         	ORIGINAL_HEYA_CD01 ,";
		$sql .="         	KASHIDASHI_KUBUN ,";
		$sql .="         	SORT_COL";
		$sql .="     	FROM";
		$sql .="         	(SELECT";
		$sql .="             	TENPO_CD ,";
		$sql .="             	ORIGINAL_HEYA_CD01 ,";
		$sql .="             	KASHIDASHI_KUBUN ,";
		$sql .="             	SORT_COL";
		$sql .="         	FROM";
		$sql .="             	(SELECT";
		$sql .="                 	/*+ FIRST_ROWS(500) PARALLEL (T, 3) */ T.TENPO_CD ,";
		$sql .="                 	ORIGINAL_HEYA_CD01 ,";
		$sql .="                 	KASHIDASHI_KUBUN ,";
		$sql .="                 	YACHIN AS SORT_COL";
		$sql .="             	FROM ";
		$sql .=                  	$whereParam["targetTbl"] ." T,";
		$sql .="                 	RENKEI_TENPO_WORK RENKEI";
		$sql .="             	WHERE";
		$sql .="     	            (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0) AND";
		$sql .="                 	RENKEI.TENPO_CD = T.TENPO_CD AND";
		$sql .="                 	RENKEI.TENPO_CD <> '00000002' AND";
		$sql .="                 	RENKEI.IKOU_FLAG = 1 AND";
		$sql .="                 	TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD')";
		$sql .="             	)";
		$sql .="         	WHERE";
		$sql .="             	ROWNUM < 1000 + 1";
		$sql .="         	ORDER BY";
		$sql .="             	SORT_COL ASC)";
		$sql .="     	WHERE";
		$sql .="         	ROWNUM < ". ($bukenCountTo + 1);
		$sql .="     	MINUS";
		$sql .="     	SELECT";
		$sql .="     	    TENPO_CD ,";
		$sql .="     	    ORIGINAL_HEYA_CD01 ,";
		$sql .="     	    KASHIDASHI_KUBUN ,";
		$sql .="     	    SORT_COL";
		$sql .="     	FROM";
		$sql .="     	    (SELECT";
		$sql .="     	        TENPO_CD ,";
		$sql .="     	        ORIGINAL_HEYA_CD01 ,";
		$sql .="     	        KASHIDASHI_KUBUN ,";
		$sql .="     	        SORT_COL";
		$sql .="     	    FROM";
		$sql .="     	        (SELECT";
		$sql .="     	            /*+ FIRST_ROWS(500) PARALLEL (T, 3) */ T.TENPO_CD ,";
		$sql .="     	            ORIGINAL_HEYA_CD01 ,";
		$sql .="     	            KASHIDASHI_KUBUN ,";
		$sql .="     	            YACHIN AS SORT_COL";
		$sql .="     	        FROM ";
		$sql .=                  	$whereParam["targetTbl"] ." T,";
		$sql .="     	            RENKEI_TENPO_WORK RENKEI";
		$sql .="     	        WHERE";
		$sql .="     	            (CONTAINS( FULL_TEXT ,'".$whereParam["freeWord"]."' ,1 ) > 0) AND";
		$sql .="     	            RENKEI.TENPO_CD = T.TENPO_CD AND";
		$sql .="     	            RENKEI.TENPO_CD <> '00000002' AND";
		$sql .="     	            RENKEI.IKOU_FLAG = 1 AND";
		$sql .="     	            TO_CHAR(IKOU_DATE, 'YYYY/MM/DD') <= TO_CHAR(SYSDATE, 'YYYY/MM/DD')";
		$sql .="     	        )";
		$sql .="     	    WHERE";
		$sql .="     	        ROWNUM < 1000 + 1";
		$sql .="     	    ORDER BY";
		$sql .="     	        SORT_COL ASC) ";
		$sql .="     	WHERE";
		$sql .="     		ROWNUM < ". $bukenCountFrom . ")";
		$sql .="    ) FREE";
		
		return $sql;
	}
	
	/**
	 * 物件数取得
	 *
	 * @access	public
	 * @param   array
	 * @return  string
	 */
	public function getBukencount($params)
	{
		$this->_model_Sort = new Rstobi_Model_Sort();
		$defaultSort = $this->_model_Sort->getDefaultSort($params);
		
		// フリーワード以外の絞込み条件がないかチェック
		if($this->chkParams($params) && $params['SORT_COLUMN'] == $defaultSort['SORT_COLUMN'] && $params['SORT_ORDER'] == $defaultSort['SORT_ORDER'])
		{
			/** プロシージャ「PRC_COUNT_FREEWORD」を利用して検索件数を取得 **/
			//普通に色々用意されてた
			//詳しくしりたいなら、下記ソースを見ればだいたいわかる
			//・アダプター関連
			//  library\Zend\Db\Adapter\Abstract.php (Zend基本)
			//  library\Zend\Db\Adapter\Oracle.php (Oracle用)
			//・ステイトメント関連
			//  library\Zend\Db\Statement.php (Zend基本)
			//  library\Zend\Db\Statement\Oracle.php (Oracle用)
			
			//コネクション取得
			$db = $this->getDbConnection();
			$stmt = $db->prepare('begin PRC_COUNT_FREEWORD(:str_word, :int_cnt); end;');
			
			//変数設定
			//bindParam(変数名, バインド値(参照渡し)、タイプ、長さ、オプション)
			//※タイプ種別は http://manual.xwd.jp/ref.oci8.html 参考
			//※タイプ以降は省略可能
			//※oracle用では _bindParam が protected で宣言されてたけど、基本側で最終的に _bindParam が呼ばれてた。。。
			$stmt->bindParam("str_word", $params['freeword']);
			$stmt->bindParam("int_cnt" , $result[0]['BUKEN_COUNT'], SQLT_CHR, 100);
			
			//実行
			$stmt->execute();
		}
		else
		{
			$db_params = array();
			
			// Where句内のフリーワード整形
			$whereParam["freeWord"] = $this->freeWordWhere($params["freeword"]);
			
			// フリーワード検索対処テーブル取得
			$whereParam["targetTbl"] = $this->getFreeWordTargetTbl();
			
			//検索条件がある、または県の指定がある場合（OracleText以外の条件指定がある場合はこちら）
			$sql  = "     select";
			$sql .= "         count(*) BUKEN_COUNT";
			$sql .= "     FROM ";
			$sql .=          $whereParam["targetTbl"];
			$sql .= "     WHERE";
			$sql .= "             (CONTAINS (FULL_TEXT,'".$whereParam["freeWord"]."',1) > 0 ) ";
			if($params['PrefCd'][0] <> '')
			{
				$db_params['strPrefCd'] = abs($params['PrefCd'][0]);
				$sql .= "         AND TODOUFUKEN = :strPrefCd";
			}
			$sql .= $this->getFreeKihonWhere($params, &$db_params);
			
			$result = $this->dbGetList($sql, $db_params);
		}
		
		return $result;
	}
	
	
	/**
	 * フリーワード以外の絞込み条件がないかチェック
	 *
	 * @access	private
	 * @param   array
	 * @return  boolean
	 */
	private function chkParams($params)
	{
		$retvalue = false;
		
		if( $params['tinryo1'] == '0'       &&
			$params['tinryo2'] == '9999999' &&
			!$this->_model_search_common->checkvalue($params,'tinryo_jouken2') &&
			!$this->_model_search_common->checkvalue($params,'tyusyajyo_komi') &&
			$params['meyasu_tinryo'] == 'normal'  &&
			!$this->_model_search_common->checkvalue($params,'freerent')       &&
			!$this->_model_search_common->checkvalue($params,'shikireizero')   &&
			!$this->_model_search_common->checkvalue($params,'shikikin_nashi') &&
			!$this->_model_search_common->checkvalue($params,'reikin_nashi')   &&
			!$this->_model_search_common->checkvalue($params,'shataku_koujyo') &&
			!$this->_model_search_common->checkvalue($params,'heyawari')       &&
			!$this->_model_search_common->checkvalue($params,'syubetu1')       &&
			!$this->_model_search_common->checkvalue($params,'syubetu2')       &&
			!$this->_model_search_common->checkvalue($params,'syubetu3')       &&
			!$this->_model_search_common->checkvalue($params,'syubetu4')       &&
			!$this->_model_search_common->checkvalue($params,'syubetu5')       &&
			$params['ekitoho'] == '0' &&
			!$this->_model_search_common->checkvalue($params,'bas_riyou')      &&
			$params['senyu1'] == '1'   &&
			$params['senyu2'] == '110' &&
			!$this->_model_search_common->checkvalue($params,'madori10')       &&
			!$this->_model_search_common->checkvalue($params,'madori11')       &&
			!$this->_model_search_common->checkvalue($params,'madori12')       &&
			!$this->_model_search_common->checkvalue($params,'madori14')       &&
			!$this->_model_search_common->checkvalue($params,'madori21')       &&
			!$this->_model_search_common->checkvalue($params,'madori22')       &&
			!$this->_model_search_common->checkvalue($params,'madori24')       &&
			!$this->_model_search_common->checkvalue($params,'madori31')       &&
			!$this->_model_search_common->checkvalue($params,'madori32')       &&
			!$this->_model_search_common->checkvalue($params,'madori34')       &&
			!$this->_model_search_common->checkvalue($params,'madori41')       &&
			!$this->_model_search_common->checkvalue($params,'madori42')       &&
			!$this->_model_search_common->checkvalue($params,'madori44')       &&
			!$this->_model_search_common->checkvalue($params,'madori51')       &&
			$params['built']  == '0' &&
			$params['jyohou'] == '0' &&
			!$this->_model_search_common->checkvalue($params,'kodawari1')      &&
			!$this->_model_search_common->checkvalue($params,'kodawari2'))
		{
			$retvalue = true;
		}
		
		return $retvalue;
	}
	
	
	/**
	 * 基本条件選択のWhere句を取得（フリーワード専用）
	 *
	 * @access private
	 */
	private function getFreeKihonWhere($params ,&$db_params)
	{
		$where = "";

		//++++++++++++++++++++++++++++++++++++++++
		//賃料
		// tinryo1        : 下限
		// tinryo2        : 上限
		// tinryo_jouken2 : 管理費・共益費込み
		// tyusyajyo_komi : 駐車場込み
		// meyasu_tinryo  : めやす賃料
		//++++++++++++++++++++++++++++++++++++++++

		//めやす賃料
		if (!empty($params['meyasu_tinryo']) && $params['meyasu_tinryo'] == 'meyasu')
		{
			$work_yachin = ' (CASE WHEN NVL(TEN_MEYASU_CHINRYO,0) != 0 THEN TEN_MEYASU_CHINRYO ELSE DECODE(NVL(MEYASU_CHINRYO,0),0,9999999,MEYASU_CHINRYO) END) ';
			
//			if($this->isOem($params)){
//				$work_yachin = " TEN_MEYASU_CHINRYO ";
//			}
//			else
//			{
//				$work_yachin = " MEYASU_CHINRYO ";
//			}
		}
		// 通常賃料
		else if(!empty($params['meyasu_tinryo']) && $params['meyasu_tinryo'] == 'normal')
		{
			$work_yachin = " YACHIN ";
		}
		
		// 賃料の下限なし・上限あり
		if (isset($params['tinryo1']) && $params['tinryo1'] == "0" && isset($params['tinryo2']) && $params['tinryo2'] <> "9999999")
		{
			$db_params['strTinryo2'] = $params['tinryo2'];
			$where .= " AND ( " . $work_yachin . " <= :strTinryo2 ) ";
		}
		//賃料の下限あり・上限なし
		elseif (isset($params['tinryo1']) && $params['tinryo1'] <> "0" && isset($params['tinryo2']) && $params['tinryo2'] == "9999999")
		{
			$db_params['strTinryo1'] = $params['tinryo1'];
			$where .= " AND ( " . $work_yachin . " >= :strTinryo1 ) ";
		}
		//賃料の下限あり・上限あり
		elseif (isset($params['tinryo1']) && $params['tinryo1'] <> "0" && isset($params['tinryo2']) && $params['tinryo2'] <> "9999999")
		{
			$db_params['strTinryo1'] = $params['tinryo1'];
			$db_params['strTinryo2'] = $params['tinryo2'];
		
			$where .= " AND ( " . $work_yachin . " BETWEEN :strTinryo1 AND :strTinryo2 ) " ;
		}
		
		//+++++++++++++++++++++++++++++++++++++++++++++
		//建物種別
		//
		// syubetu1 : 貸家アパート
		// syubetu2 : 貸家マンション
		// syubetu3 : 貸家一戸建て・借家
		// syubetu4 : 貸店舗・賃貸事務所
		// syubetu5 : 駐車場
		//+++++++++++++++++++++++++++++++++++++++++++++

		if (!empty($params['syubetu1']) || !empty($params['syubetu2']) || !empty($params['syubetu3']) ||
			!empty($params['syubetu4']) || !empty($params['syubetu5']) )
		{
			$aryTatemonoCd = array();
			
			//貸家アパート (アパート、ハイツ、コーポ)
			if (!empty($params['syubetu1']) && $params['syubetu1'] == "on")
			{
				array_push($aryTatemonoCd, Apm_Config_Search::TATEMONO_APART
										  ,Apm_Config_Search::TATEMONO_HEIGHTS
										  ,Apm_Config_Search::TATEMONO_CORP
										  ,Apm_Config_Search::TATEMONO_GESYUKU);
			}
			//貸家マンション (マンション)
			if (!empty($params['syubetu2']) && $params['syubetu2'] == "on")
			{
				array_push($aryTatemonoCd, Apm_Config_Search::TATEMONO_MANSION);
			}
			//貸家一戸建て・借家 (テラスハウス、タウンハウス、一戸建て、貸家、長屋)
			if (!empty($params['syubetu3']) && $params['syubetu3'] == "on")
			{
				array_push($aryTatemonoCd, Apm_Config_Search::TATEMONO_TERRACE
										  ,Apm_Config_Search::TATEMONO_TOWN
										  ,Apm_Config_Search::TATEMONO_IKKODATE
										  ,Apm_Config_Search::TATEMONO_KASIYA
										  ,Apm_Config_Search::TATEMONO_NAGAYA);
			}
			//貸店舗・賃貸事務所 (店舗、事務所、店舗・事務所、店舗付住宅、事業用建物、事業用土地、その他土地)
			if (!empty($params['syubetu4']) && $params['syubetu4'] == "on")
			{
				array_push($aryTatemonoCd, Apm_Config_Search::TATEMONO_TENPO
										  ,Apm_Config_Search::TATEMONO_JIMUSYO
										  ,Apm_Config_Search::TATEMONO_TENPO_JIMUSYO
										  ,Apm_Config_Search::TATEMONO_TENPO_JYUTAKU
										  ,Apm_Config_Search::TATEMONO_JIGYO_TATEMONO
										  ,Apm_Config_Search::TATEMONO_JIGYO_TOCHI
										  ,Apm_Config_Search::TATEMONO_SONOTA_TOCHI);
			}
			//駐車場 (駐車場)
			if (!empty($params['syubetu5']) && $params['syubetu5'] == "on")
			{
				array_push($aryTatemonoCd, Apm_Config_Search::TATEMONO_TYUSYAJYO);
			}
			
			$where .= " AND TATEMONO in (";
			for($i=0; $i<count($aryTatemonoCd); $i++)
			{
				$db_params['strTatemonoCd_'.$i] = $aryTatemonoCd[$i];
				$where .= " :strTatemonoCd_".$i." ,";
			}
			// 最後のカンマを削除
			$where = substr($where, 0, (strlen($where)-1) );
			$where .= ") ";
			
		}
		//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		//駅徒歩
		//
		// ekitoho   : １分以内、５分以内、７分以内、１０分以内、１５分以内、指定なし
		//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

		//指定した場合
		if (!empty($params['ekitoho']))
		{
			$db_params['strEkitoho'] = $params['ekitoho'];
			
			$where .= " AND ( ARUKI <= :strEkitoho ) " ;
		}

		//+++++++++++++++++++
		//専有面積
		//
		// senyu1 : 下限
		// senyu2 : 上限
		//+++++++++++++++++++

		if (!empty($params['senyu1']) && !empty($params['senyu2']) )
		{
			if ($params['senyu1'] == "1")
			{
				$params['senyu1'] = "0";
			}
			// 専有面積の下限なし・上限あり
			if ($params['senyu1'] == "0" && $params['senyu2'] <> "99999")
			{
				$db_params['strSenyu2'] = $params['senyu2'];
				$where .= " AND ( MENSEKI <= :strSenyu2 ) ";
			}
			// 専有面積の下限あり・上限なし
			elseif ($params['senyu2'] == "99999" && $params['senyu1'] <> "0")
			{
				$db_params['strSenyu1'] = $params['senyu1'];
				$where .= " AND ( MENSEKI >= :strSenyu1 ) ";
			}
			// 専有面積の下限あり・上限あり
			else if($params['senyu1'] <> "0" && $params['senyu2'] <> "99999")
			{
				$db_params['strSenyu1'] = $params['senyu1'];
				$db_params['strSenyu2'] = $params['senyu2'];
				
				$where .= " AND ( MENSEKI BETWEEN :strSenyu1 AND :strSenyu2 ) ";
			}
			if ($params['senyu1'] == "0")
			{
				$params['senyu1'] = 1;
			}
		}

		//+++++++++++++++++++++++++++
		//間取り
		//
		// madori10 : 1R
		// madori11 : 1K
		// madori12 : 1DK,1SDK
		// madori14 : 1LDK,1SLDK
		// madori21 : 2R,2K
		// madori22 : 2DK,2SDK
		// madori24 : 2LDK,2SLDK
		// madori31 : 3R,3K
		// madori32 : 3DK,3SDK
		// madori34 : 3LDK,3SLDK
		// madori41 : 4R,4K
		// madori42 : 4DK,4SDK
		// madori44 : 4LDK,4SLDK
		// madori51 : 5R～7SLDK
		//+++++++++++++++++++++++++++

		if (!empty($params['madori10']) || !empty($params['madori11']) || !empty($params['madori12']) || !empty($params['madori14']) ||
			!empty($params['madori21']) || !empty($params['madori22']) || !empty($params['madori24']) ||
			!empty($params['madori31']) || !empty($params['madori32']) || !empty($params['madori34']) ||
			!empty($params['madori41']) || !empty($params['madori42']) || !empty($params['madori44']) || !empty($params['madori51']) )
		{
			$aryMadoriCd = array();
			
			//1R
			if (!empty($params['madori10']) && $params['madori10'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_1R);
			}
			//1K
			if (!empty($params['madori11']) && $params['madori11'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_1K);
			}
			//1DK(+S)
			if (!empty($params['madori12']) && $params['madori12'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_1DK, Apm_Config_Search::MADORI_1SDK);
			}
			//1LDK(+S)
			if (!empty($params['madori14']) && $params['madori14'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_1LDK, Apm_Config_Search::MADORI_1SLDK);
			}
			//2K
			if (!empty($params['madori21']) && $params['madori21'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_2R, Apm_Config_Search::MADORI_2K);
			}
			//2DK(+S)
			if (!empty($params['madori22']) && $params['madori22'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_2DK, Apm_Config_Search::MADORI_2SDK);
			}
			//2LDK(+S)
			if (!empty($params['madori24']) && $params['madori24'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_2LDK, Apm_Config_Search::MADORI_2SLDK);
			}
			//3K
			if (!empty($params['madori31']) && $params['madori31'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_3R, Apm_Config_Search::MADORI_3K);
			}
			//3DK(+S)
			if (!empty($params['madori32']) && $params['madori32'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_3DK, Apm_Config_Search::MADORI_3SDK);
			}
			//3LDK
			if (!empty($params['madori34']) && $params['madori34'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_3LDK, Apm_Config_Search::MADORI_3SLDK);
			}
			//4K
			if (!empty($params['madori41']) && $params['madori41'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_4R, Apm_Config_Search::MADORI_4K);
			}
			//4DK(+S)
			if (!empty($params['madori42']) && $params['madori42'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_4DK, Apm_Config_Search::MADORI_4SDK);
			}
			//4LDK
			if (!empty($params['madori44']) && $params['madori44'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_4LDK, Apm_Config_Search::MADORI_4SLDK);
			}
			//5K～
			if (!empty($params['madori51']) && $params['madori51'] == "on")
			{
				array_push($aryMadoriCd, Apm_Config_Search::MADORI_5R
										,Apm_Config_Search::MADORI_5R
										,Apm_Config_Search::MADORI_5K
										,Apm_Config_Search::MADORI_5DK
										,Apm_Config_Search::MADORI_5SDK
										,Apm_Config_Search::MADORI_5LDK
										,Apm_Config_Search::MADORI_5SLDK
										,Apm_Config_Search::MADORI_6R
										,Apm_Config_Search::MADORI_6K
										,Apm_Config_Search::MADORI_6DK
										,Apm_Config_Search::MADORI_6SDK
										,Apm_Config_Search::MADORI_6LDK
										,Apm_Config_Search::MADORI_6SLDK
										,Apm_Config_Search::MADORI_7R
										,Apm_Config_Search::MADORI_7K
										,Apm_Config_Search::MADORI_7DK
										,Apm_Config_Search::MADORI_7SDK
										,Apm_Config_Search::MADORI_7LDK
										,Apm_Config_Search::MADORI_7SLDK);
			}
			
			$where .= " AND MADORI in (";
			for($i=0; $i<count($aryMadoriCd); $i++)
			{
				$db_params['strMadoriCd_'.$i] = $aryMadoriCd[$i];
				$where .= " :strMadoriCd_".$i." ,";
			}
			// 最後のカンマを削除
			$where = substr($where, 0, (strlen($where)-1) );
			$where .= ") ";
		}

		//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		//築年数
		//
		// built : 新築、3年以内、5年以内、10年以内、指定なし
		//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

		if (!empty($params['built']))
		{
			if ($params['built'] == "1")
			{
				$where .= " AND SHINCHAKU = 1 ";
			}
			elseif ($params['built'] <> "0")
			{
				$db_params['strBuilt'] = $params['built'];
				$where .= " AND ( TIKU_NENSU <= :strBuilt )";
			}
		}
		//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		//情報の新しさ
		//
		// jyohou : 本日更新、3日以内更新、1週間以内更新、指定なし
		//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		
		if (!empty($params['jyohou']) && $params['jyohou'] <> "0" )
		{
			$db_params['strJyohou'] = $params['jyohou'];
			$where .=  " AND ( TO_DATE( TO_CHAR(KOUSINBI, 'YYYYMMDD') ) > TO_DATE( TO_CHAR( SYSDATE - :strJyohou , 'YYYYMMDD') ) ) ";
		}
		
		//++++++++
		//画像
		//++++++++
		if (!empty($params['kodawari1']) && $params['kodawari1'] == "on")
		{
			$where .= " AND (   NAISOU_GAZOU_COMMON01 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON02 <> 0"; 
			$where .= "      OR NAISOU_GAZOU_COMMON03 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON04 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON05 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON06 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON07 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON08 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON09 <> 0";
			$where .= "      OR NAISOU_GAZOU_COMMON10 <> 0)";
		}
		if (!empty($params['kodawari2']) && $params['kodawari2'] == "on")
		{
			$where .= " AND (   PANORAMA_GAZOU_COMMON01 <> 0";
			$where .= "      OR PANORAMA_GAZOU_COMMON02 <> 0)";
		}
		
		return $where;
	}

}
?>